package com.mrh.se2_contactasauraus;

import java.util.ArrayList;
import java.util.List;

import com.mrh.se2_contactasauraus.Database.FeedEntry;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHandler extends SQLiteOpenHelper {
	// Names for category table
	public static final String TABLE_NAME1 = "cat";
	public static final String COLUMN_NAME_CAT_NAME = "cat_name";
	public static final String COLUMN_NAME_CAT_ID = "cat_id";

	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "contactsManager.db";

	// Contacts table name
	private static final String TABLE_CONTACTS = "contacts";

	// Contacts Table Columns names
	private static final String KEY_ID = "id";
	private static final String KEY_NAME = "name";
	private static final String KEY_EMAIL = "email";
	private static final String KEY_ADDRESS = "address";

	private static final String KEY_PH_NO = "phone_number";
	public static final String KEY_ALT_NO = "alt_number";

	public static final String KEY_COMMENT = "comment";
	
	public static final String KEY_CAT = "category";

	public DatabaseHandler(Context context) {
		// Log.d("DBC", "Database Constructor");
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.d("CNT", "Creating New Table");
		String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
				+ KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_NAME
				+ " TEXT,"

				+ KEY_ADDRESS + " TEXT,"

				+ KEY_PH_NO + " TEXT, " 
				+ KEY_ALT_NO + " TEXT, "

				+ KEY_EMAIL + " TEXT, "

				+ KEY_COMMENT + " TEXT, "
				
				+ KEY_CAT + " INTEGER"

				+ ")";

		String TABLE_NAME1 = "cat";
		String CREATE_TABLE_CAT = "CREATE TABLE " + TABLE_NAME1 + " ("
				+ FeedEntry.COLUMN_NAME_CAT_ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ FeedEntry.COLUMN_NAME_CAT_NAME + " TEXT);";

		db.execSQL(CREATE_CONTACTS_TABLE);
		db.execSQL(CREATE_TABLE_CAT);

		db.execSQL("INSERT INTO " + TABLE_NAME1 + "(" + COLUMN_NAME_CAT_NAME + ") "
				+ " VALUES (\"1\");");
		db.execSQL("INSERT INTO " + TABLE_NAME1 + "(" + COLUMN_NAME_CAT_NAME + ") "
				+ " VALUES (\"2\");");
		db.execSQL("INSERT INTO " + TABLE_NAME1 + "(" + COLUMN_NAME_CAT_NAME + ") "
				+ " VALUES (\"3\");");
		db.execSQL("INSERT INTO " + TABLE_NAME1 + "(" + COLUMN_NAME_CAT_NAME + ") "
				+ " VALUES (\"4\");");
		db.execSQL("INSERT INTO " + TABLE_NAME1 + "(" + COLUMN_NAME_CAT_NAME + ") "
				+ " VALUES (\"5\");");
//		db.close();

	}

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		Log.d("DOT", "Dropping Old Tables");
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);

		// Create tables again
		onCreate(db);
	}

	// Adding new contact
	public void addContact(Contact contact) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_NAME, contact.getName()); // Contact Name
		values.put(KEY_ADDRESS, contact.get_address()); // Contact Name
		values.put(KEY_EMAIL, contact.get_email()); // Contact Name
		
		values.put(KEY_PH_NO, contact.getPhoneNumber());
		values.put(KEY_ALT_NO, contact.get_alt_number());
		
		values.put(KEY_COMMENT, contact.get_comment());
		
		values.put(KEY_CAT, contact.get_cat());

		// Inserting Row
		db.insert(TABLE_CONTACTS, null, values);
		db.close(); // Closing database connection
	}

	public void editCategories(String[] x) {
		SQLiteDatabase db = this.getWritableDatabase();

		db.execSQL("UPDATE " + TABLE_NAME1
				+ " SET cat_name = '" + x[0]
				+ "' WHERE cat_id = 1");
		db.execSQL("UPDATE " + TABLE_NAME1
				+ " SET cat_name = '" + x[1]
				+ "' WHERE cat_id = 2");
		db.execSQL("UPDATE " + TABLE_NAME1
				+ " SET cat_name = '" + x[2]
				+ "' WHERE cat_id = 3");
		db.execSQL("UPDATE " + TABLE_NAME1
				+ " SET cat_name = '" + x[3]
				+ "' WHERE cat_id = 4");
		db.execSQL("UPDATE " + TABLE_NAME1
				+ " SET cat_name = '" + x[4]
				+ "' WHERE cat_id = 5");

		db.close(); // Closing database connection

	}
	
	public String[] getCategoryNames() {
		
		SQLiteDatabase db = this.getReadableDatabase();
		
		String selectQuery = "SELECT * FROM " + TABLE_NAME1 + " ORDER BY cat_id ASC";;
		
		Cursor cursor = db.rawQuery(selectQuery, null);
		
		if (cursor != null) {
			cursor.moveToFirst();
		}
		
		String[] s = new String[5];
		int i = 0;
		do {
			s[i++] = cursor.getString(1);
		} while (cursor.moveToNext());
		
		
		cursor.close();
		db.close();
		
		return s;
		
		
	}
	

	public Contact getContact(int id) {
		Log.d("WW", "We have arrived");
		SQLiteDatabase db = this.getReadableDatabase();
		Log.d("WW", "We have passed");
		
		Contact contact = null;
		Log.d("WW", "We have pp");
		
		String selectQuery = "SELECT * FROM " + TABLE_CONTACTS
				+ " WHERE id = '" + id + "'";
		
		Cursor cursor = db.rawQuery(selectQuery, null);
		
		Log.d("WW", "We have pp");
		if (cursor != null) {
			cursor.moveToFirst();

		contact = new Contact();
					
		int u = Integer.parseInt(cursor.getString(0));
		Log.d("t0", u + "");
		contact.setID(u);
		
		String t = cursor.getString(1);
		Log.d("t1", t);
		contact.setName(t);
		
		t = cursor.getString(2);
		Log.d("t2", t);
		contact.set_address(t);
				
		t = cursor.getString(3);
		Log.d("t3", t);
		contact.setPhoneNumber(t);
		
		t = cursor.getString(4);
		Log.d("t4", t);
		contact.set_alt_number(t);
		
		t = cursor.getString(5);
		Log.d("t5", t);
		contact.set_email(t);
		
		t = cursor.getString(6);
		Log.d("t6", t);
		contact.set_comment(t);
		
		t = cursor.getString(7);
		Log.d("t7", t);
		contact.set_cat(Integer.parseInt(t));
		
		}
		
		cursor.close();
		db.close();
		
		// return contact
		return contact;
	}

	public ArrayList<Contact> getAllContacts() {
		Log.d("GAC", "Get all contacts");
		ArrayList<Contact> contactList = new ArrayList<Contact>();
		// Select All Query
		Log.d("SS", "Select *");
		String selectQuery = "SELECT * FROM " + TABLE_CONTACTS
				+ " ORDER BY name DESC";

		Log.d("GWDB", "Get writeable");
		SQLiteDatabase db = this.getWritableDatabase();
		Log.d("NC", "New cursor");
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Contact contact = new Contact();
				contact.setID(Integer.parseInt(cursor.getString(0)));
				contact.setName(cursor.getString(1));
				contact.set_address(cursor.getString(2));
				contact.setPhoneNumber(cursor.getString(3));
				contact.set_alt_number(cursor.getString(4));
				contact.set_email(cursor.getString(5));
				contact.set_comment(cursor.getString(6));
				contact.set_cat(Integer.parseInt(cursor.getString(7)));
				// Adding contact to list
				contactList.add(contact);
			} while (cursor.moveToNext());
		}

		cursor.close();

		db.close();

		Log.d("RCL", "Return contact list");

		// return contact list
		return contactList;
	}

	public ArrayList<Contact> getSearchContacts(String search) {
		Log.d("GAC", "Get all contacts");
		ArrayList<Contact> contactList = new ArrayList<Contact>();
		// Select All Query
		Log.d("SS", "SELECT * FROM " + TABLE_CONTACTS + " WHERE name LIKE '"
				+ search + "' OR " + KEY_PH_NO + " LIKE '" + search + "' ORDER BY name DESC");
		String selectQuery = "SELECT * FROM " + TABLE_CONTACTS + " WHERE name LIKE '%"
				+ search + "%' OR " + KEY_PH_NO + " LIKE '%" + search + "%' ORDER BY name DESC";

		Log.d("GWDB", "Get writeable");
		SQLiteDatabase db = this.getWritableDatabase();
		Log.d("NC", "New cursor");
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Contact contact = new Contact();
				contact.setID(Integer.parseInt(cursor.getString(0)));
				contact.setName(cursor.getString(1));
				contact.set_address(cursor.getString(2));
				contact.setPhoneNumber(cursor.getString(3));
				contact.set_alt_number(cursor.getString(4));
				contact.set_email(cursor.getString(5));
				contact.set_comment(cursor.getString(6));
				// Adding contact to list
				contactList.add(contact);
			} while (cursor.moveToNext());
		}

		cursor.close();

		db.close();

		Log.d("RCL", "Return contact list = " + contactList.size());

		// return contact list
		return contactList;
	}
	
	public ArrayList<Contact> getCatContacts(int cat) {
		Log.d("GAC", "Get all contacts");
		ArrayList<Contact> contactList = new ArrayList<Contact>();
		// Select All Query
		Log.d("SS", "SELECT * FROM " + TABLE_CONTACTS + " WHERE " + KEY_CAT + " = "
				+ cat + " ORDER BY name DESC");
		String selectQuery = "SELECT * FROM " + TABLE_CONTACTS + " WHERE " + KEY_CAT + " = "
				+ cat + " ORDER BY name DESC";

		Log.d("GWDB", "Get writeable");
		SQLiteDatabase db = this.getWritableDatabase();
		Log.d("NC", "New cursor");
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Contact contact = new Contact();
				contact.setID(Integer.parseInt(cursor.getString(0)));
				contact.setName(cursor.getString(1));
				contact.set_address(cursor.getString(2));
				contact.setPhoneNumber(cursor.getString(3));
				contact.set_alt_number(cursor.getString(4));
				contact.set_email(cursor.getString(5));
				contact.set_comment(cursor.getString(6));
				// Adding contact to list
				contactList.add(contact);
			} while (cursor.moveToNext());
		}

		cursor.close();

		db.close();

		Log.d("RCL", "Return contact list = " + contactList.size());

		// return contact list
		return contactList;
	}
	
	public int getContactsCount() {
		String countQuery = "SELECT  * FROM " + TABLE_CONTACTS;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		cursor.close();
		db.close();

		// return count
		return cursor.getCount();
	}

	public int updateContact(Contact contact) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_NAME, contact.getName());
		
		values.put(KEY_PH_NO, contact.getPhoneNumber());
		values.put(KEY_ALT_NO, contact.get_alt_number());
		
		values.put(KEY_ADDRESS, contact.get_address());
		values.put(KEY_CAT, contact.get_cat());
		values.put(KEY_COMMENT, contact.get_comment());
		values.put(KEY_EMAIL, contact.get_email());

		// updating row
		int i = db.update(TABLE_CONTACTS, values, KEY_ID + " = ?",
				new String[] { String.valueOf(contact.getID()) });
		
		db.close();
		
		return i;
	}

	public void deleteContact(Contact contact) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_CONTACTS, KEY_ID + " = ?",
				new String[] { String.valueOf(contact.getID()) });
		db.close();
	}

	// close the database connection
	public void close() {
		SQLiteDatabase db = this.getWritableDatabase();
		if (db != null)
			db.close(); // close the database connection
	} // end method close
	
	int retreiveLatest()
	{
		int toReturn = -1;
		
		ArrayList<Contact> contactList = new ArrayList<Contact>();
		String selectQuery = "SELECT * FROM " + TABLE_CONTACTS
				+ " ORDER BY id DESC";


		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {

				toReturn = Integer.parseInt(cursor.getString(0));

		}
		cursor.close();

		db.close();

		// return contact list
		return toReturn;
		
	}
	

	public ArrayList<String> getAllCategories() {
		ArrayList<String> catList = new ArrayList<String>();

		String selectQuery = "SELECT * FROM " + TABLE_NAME1;


		SQLiteDatabase db = this.getWritableDatabase();

		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
		
				String x = cursor.getString(1);

				catList.add(x);
				
				
			} while (cursor.moveToNext());
		}

		cursor.close();

		db.close();

		// return contact list
		return catList;
	}
	
	
	
}